﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

using MicroDb.Basis;
using MicroDb.Options;

using Microsoft.Extensions.Logging;

namespace MicroDb.MySql
{
    class MySqlTableExecute : TableExecute
    {
        private SqlExecute execute;
        private readonly ILogger<TableExecute> logger;
        private readonly Dictionary<string, string> dataTypeMaps;
        public MySqlTableExecute(SqlExecute execute, ILogger<TableExecute> logger = null)
        {

            this.execute = execute;
            this.logger = logger;
            dataTypeMaps = GetDateTypeMapping();
        }

        public override string GetCurrentDb(MicroDbOptions microDb)
        {
            return new MySqlConnector.MySqlConnectionStringBuilder(microDb.DbConnectionString).Database;
        }

        public override List<TableInfo> GetTables(string dbName)
        {
            return execute.ExecuteList<TableInfo>(CommandType.Text, $"SELECT TABLE_NAME AS `table_name`,TABLE_COMMENT AS `comment`,ENGINE as `engine` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = '{dbName}';", null);
        }

        public override ColumnInfo[] GetColumns(string dbName, string tableName)
        {
            using (var reader = execute.ExecuteReader(CommandType.Text, "SELECT COLUMN_NAME ,DATA_TYPE,COLUMN_COMMENT,IS_NULLABLE,COLUMN_KEY,EXTRA,COLUMN_TYPE,CHARACTER_SET_NAME,COLUMN_DEFAULT,IFNULL(CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION) as Length  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tableName ORDER BY ORDINAL_POSITION", new Dictionary<string, object>() { { "@schema", dbName }, { "@tableName", tableName } }))
            {
                var list = new List<ColumnInfo>();
                while (reader.Read())
                {
                    var columnType = reader["COLUMN_TYPE"].ToString();
                    var dbType = reader["DATA_TYPE"].ToString();
                    var defaultValue = reader["COLUMN_DEFAULT"]?.ToString();
                    var dbLength = reader["Length"]?.ToString();
                    int.TryParse(dbLength, out int length);
                    if (string.IsNullOrEmpty(defaultValue))
                    {
                        defaultValue = null;
                    }
                    else
                    {
                        defaultValue = $" DEFAULT '{defaultValue}'";
                    }
                    if (!dataTypeMaps.TryGetValue(columnType, out string dataType))
                    {
                        dataTypeMaps.TryGetValue(dbType, out dataType);
                    }
                    ColumnInfo columnInfo = new ColumnInfo()
                    {
                        ColumnName = reader["COLUMN_NAME"].ToString(),
                        Comment = reader["COLUMN_COMMENT"].ToString(),
                        DbType = columnType,
                        DataType = dataType ?? "string",
                        DateLength = length,
                        IsNullable = reader["IS_NULLABLE"].ToString().Equals("YES", StringComparison.OrdinalIgnoreCase),
                        IsIncrement = reader["EXTRA"].ToString().Equals("auto_increment", StringComparison.OrdinalIgnoreCase),
                        IsPrimaryKey = reader["COLUMN_KEY"].ToString().Equals("PRI", StringComparison.OrdinalIgnoreCase),
                        ColumnKey = reader["COLUMN_KEY"].ToString(),
                    };
                    var dict = new Dictionary<string, string>();
                    dict.Add("cf-extend", $"{columnType}{GetChar(reader)}{(defaultValue ?? (!columnInfo.IsNullable ? " NOT NULL" : " DEFAULT NULL"))}{(columnInfo.IsIncrement ? " AUTO_INCREMENT" : "")} ");

                    columnInfo.CodeFist = dict;
                    list.Add(columnInfo);
                }
                return list.ToArray();
            }
        }
        private string GetChar(DbDataReader reader)
        {
            var CHARACTER_SET_NAME = reader["CHARACTER_SET_NAME"]?.ToString();
            if (CHARACTER_SET_NAME == null || CHARACTER_SET_NAME == "" || CHARACTER_SET_NAME == "utf8")
            {
                return "";
            }
            return " CHARACTER SET utf8mb4 ";
        }
        public override List<string> GetDbList()
        {
            return execute.ExecuteList<string>(CommandType.Text, "SHOW DATABASES;", null);
        }


        public override Dictionary<string, string> GetTableCodeFirst(string dbName, string tableName)
        {
            var dict = new Dictionary<string, string>();
            using (var reader = execute.ExecuteReader(CommandType.Text, $"SHOW CREATE TABLE {dbName}.{tableName};", null))
            {
                if (!reader.Read())
                    return dict;
                var createSql = reader["Create Table"].ToString();
                var strs = ClearSqlStr(createSql);
                var indexs = new StringBuilder();
                foreach (var item in strs)
                {
                    if (item.StartsWith(")"))
                    {
                        dict.Add("cf-extend", item.TrimStart(')'));
                    }
                    else
                    {
                        indexs.Append(item);
                    }
                }
                dict.Add("cf-indexs", indexs.ToString());
            }
            return dict;
        }

        public override void CreateDb(string dbName)
        {
            execute.ExecuteNonQuery(CommandType.Text, $"CREATE DATABASE `{dbName}` CHARACTER SET utf8 COLLATE utf8_general_ci;", null);
        }

        public override void CreateTable(CodeFirstParentInfo tableInfo)
        {
            string ps = string.Join("\r\n,", tableInfo.Children.Select(GetColSql));
            string indexs = string.Join("", "," + tableInfo.Attributes.GetDefalutValue("cf-indexs", ""));
            string sql = $"CREATE TABLE `{ChangeNameAs(tableInfo.Name)}` ({ps}{indexs}) {tableInfo.Attributes.GetDefalutValue("cf-extend", "")};";
            execute.ExecuteNonQuery(CommandType.Text, sql, null);
        }

        private string GetColSql(CodeFirstInfo info)
        {
            return $"`{ChangeNameAs(info.Name)}` {info.Attributes["cf-extend"]} COMMENT '{(info.Attributes.GetDefalutValue("summary", "").Trim())}'";
        }

        public override void AlterColumns(string dbName, string tableName, CodeFirstInfo[] columns)
        {
            var oldCols = GetColumns(dbName, tableName);
            var newCols = new List<CodeFirstInfo>();
            foreach (var col in columns)
            {
                string colName = ChangeNameAs(col.Name);
                var old = oldCols.FirstOrDefault(a => a.ColumnName == colName);
                if (old == null)
                {
                    newCols.Add(col);
                }
                else
                {
                    if (!col.Attributes["cf-extend"].Contains(old.DbType))
                    {
                        logger.LogInformation($"字段 `{tableName}`.`{colName}` 属性发生变化，请手动修改");
                    }
                }
            }
            if (newCols.Count > 0)
            {
                execute.ExecuteNonQuery(CommandType.Text, string.Join("", newCols.Select(col => $"ALTER TABLE `{tableName}` ADD {GetColSql(col)};")), null);
            }
        }

        public override Dictionary<string, string> GetDateTypeMapping()
        {
            return new Dictionary<string, string>
            {
                { string.Empty,        "string" }, // default
                { "bigint unsigned",   "decimal" },
                { "bigint",            "long" },
                { "binary",            "byte[]" },
                { "bit",               "long" },
                { "bit(1)",            "bool" },
                { "blob",              "byte[]" },
                { "bool",              "bool" },
                { "boolean",           "bool" },
                { "char byte",         "byte[]" },
                { "char",              "string" },
                { "character varying", "string" },
                { "date",              "DateTime" },
                { "datetime",          "DateTime" },
                { "datetimeoffset",    "DateTimeOffset" },
                { "dec",               "decimal" },
                { "decimal",           "decimal" },
                { "double unsigned",   "decimal" },
                { "json",              "Newtonsoft.Json.Linq.JToken"},
                { "double",            "double" },
                { "enum",              "string" },
                { "fixed",             "decimal" },
                { "float unsigned",    "decimal" },
                { "float",             "float" },
                { "geography",         string.Empty },
                { "geometry",          string.Empty },
                { "int unsigned",      "long" },
                { "int",               "int" },
                { "integer unsigned",  "long" },
                { "integer",           "int" },
                { "longblob",          "byte[]" },
                { "longtext",          "string" },
                { "mediumblob",        "byte[]" },
                { "mediumint",         "int" },
                { "mediumtext",        "string" },
                { "national char",     "string" },
                { "national varchar",  "string" },
                { "nchar",             "string" },
                { "numeric",           "decimal" },
                { "nvarchar",          "string" },
                { "real",              "double" },
                { "serial",            "decimal" },
                { "set",               "string" },
                { "smallint unsigned", "int" },
                { "smallint",          "short" },
                { "text",              "string" },
                { "time",              "TimeSpan" },
                { "timestamp",         "DateTime" },
                { "tinyblob",          "byte[]" },
                { "tinyint unsigned",  "byte" },
                { "tinyint",           "sbyte" },
                { "tinytext",          "string" },
                { "varbinary",         "byte[]" },
                { "varchar",           "string" },
                { "year",              "short" }
            };
        }

        private static List<string> ClearSqlStr(string str)
        {
            var sr = new System.IO.StringReader(str);
            var res = new List<string>();
            // 跳过第一行
            string line = sr.ReadLine();
            while ((line = sr.ReadLine()) != null)
            {
                line = line.TrimStart();
                if (!line.StartsWith("`") || line.StartsWith(")"))
                {
                    res.Add(line);
                }
            }
            return res;
        }

    }
    // SqLiteToCSharp
    //public Dictionary<string, string> GetMapping()
    //{
    //    return new Dictionary<string, string>
    //        {
    //            { string.Empty, "string" }, // default
    //            { "bigint", "long" },
    //            { "blob", "byte[]" },
    //            { "boolean", "bool" },
    //            { "character", "string" },
    //            { "clob", "string" },
    //            { "date", "DateTime" },
    //            { "datetime", "DateTime" },
    //            { "decimal", "double" },
    //            { "double", "double" },
    //            { "double precision", "double" },
    //            { "float", "double" },
    //            { "int", "long" },
    //            { "int2", "long" },
    //            { "int8", "long" },
    //            { "integer", "long" },
    //            { "mediumint", "long" },
    //            { "native character", "string" },
    //            { "nchar", "string" },
    //            { "numeric", "decimal" },
    //            { "nvarchar", "string" },
    //            { "real", "double" },
    //            { "smallint", "int" },
    //            { "text", "string" },
    //            { "unsigned big int", "long" },
    //            { "varchar", "string" },
    //            { "varying character", "string" }
    //        };
    //}

    // SqlServerToCSharp
    //public Dictionary<string, string> GetMapping()
    //{
    //    return new Dictionary<string, string>
    //        {
    //            { string.Empty,       "string" }, // default
    //            { "bigint",           "long" },
    //            { "binary",           "byte[]" },
    //            { "bit",              "bool" },
    //            { "date",             "DateTime" },
    //            { "datetime",         "DateTime" },
    //            { "datetime2",        "DateTime" },
    //            { "datetimeoffset",   "DateTimeOffset" },
    //            { "decimal",          "decimal" },
    //            { "float",            "double" },
    //            { "hierarchyid",      "Hierarchy.HierarchyId" },
    //            { "image",            "byte[]" },
    //            { "int",              "int" },
    //            { "money",            "decimal" },
    //            { "numeric",          "decimal" },
    //            { "real",             "float" },
    //            { "smalldatetime",    "DateTime" },
    //            { "smallint",         "short" },
    //            { "smallmoney",       "decimal" },
    //            { "table type",       "DataTable" },
    //            { "time",             "TimeSpan" },
    //            { "timestamp",        "byte[]" },
    //            { "tinyint",          "byte" },
    //            { "uniqueidentifier", "Guid" },
    //            { "varbinary",        "byte[]" },
    //            { "varbinary(max)",   "byte[]" },
    //            { "geography",        string.Empty },
    //            { "geometry",         string.Empty }
    //        };
    //}


    // OracleToCSharp
    //public Dictionary<string, string> GetMapping()
    //{
    //    return new Dictionary<string, string>
    //        {
    //            { string.Empty,                     "string" }, // default
    //            { "binary_double",                  "decimal" },
    //            { "binary_float",                   "double" },
    //            { "binary_integer",                 "long" },
    //            { "blob",                           "byte[]" },
    //            { "char",                           "string" },
    //            { "clob",                           "string" },
    //            { "date",                           "DateTime" },
    //            { "float",                          "double" },
    //            { "interval day to second",         "decimal" },
    //            { "interval year to month",         "decimal" },
    //            { "long raw",                       "byte[]" },
    //            { "long",                           "long" },
    //            { "nchar",                          "string" },
    //            { "nclob",                          "string" },
    //            { "number",                         "decimal" },
    //            { "nvarchar2",                      "string" },
    //            { "pls_integer",                    "long" },
    //            { "raw",                            "byte[]" },
    //            { "real",                           "float" },
    //            { "rowid",                          "string" },
    //            { "timestamp with local time zone", "DateTime" },
    //            { "timestamp with time zone",       "DateTime" },
    //            { "timestamp",                      "DateTime" },
    //            { "urowid",                         "string" },
    //            { "varchar2",                       "string" },
    //            { "xmltype",                        "string" }
    //        };
    //}
}
